package cn.edu.scau.cmi.liangzaoqing.util;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.metamodel.DataContext;
import org.apache.metamodel.DataContextFactory;
import org.apache.metamodel.schema.Column;
import org.apache.metamodel.schema.Relationship;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;

//对数据库操作的工具
public class ObtainDataBaseMetadataUtil {

	public ArrayList<Table> getReferencedTable(Table tableName) {//找到主动引用表，此方法并没有使用到
		Schema schema = tableName.getSchema();
		ArrayList<Table> tableRefencerd = new ArrayList<Table>();
		Table[] tables = schema.getTables();
		for (Table table1 : tables) {
			if (!table1.equals(tableName)) {//没有考虑自引用！
				Relationship[] relationships = table1.getRelationships();
				for (Relationship relationship : relationships) {
					Table foreignTable = relationship.getForeignTable();
					if(foreignTable.equals(tableName)) {
						tableRefencerd.add(table1);
					}
					/*Column[] columns = relationship.getForeignColumns();
					for (Column column : columns) {
						if (column.getName().equals(tableName.getName())) {//该方法具有局限性，只有在外键名字与其引用的表的名字相同时才能不出错
							tableRefencerd.add(table1);//比如book2room表痛过book饮用book表，但是housecertificate表通过depart引用department时不适用
						}							   //总结：外键与引用表需要严格一致。没有考虑自引用！
					}*/
				}
			}
		}
		return tableRefencerd;
	}

	@SuppressWarnings("null")
	public Table[] getReferencedTable01(Table tableName) {//此方法并没有使用到
		Schema schema = tableName.getSchema();
		ArrayList<Table> referencedTable = new ArrayList<Table>();
		Table[] tables = schema.getTables();
		for (Table table1 : tables) {
			if (!table1.equals(tableName)) {
				Relationship[] relationships = table1.getRelationships();
				for (Relationship relationship : relationships) {
					Table foreignTable = relationship.getForeignTable();
					if(foreignTable.equals(tableName)) {
						referencedTable.add(table1);
					}
					/*Column[] columns = relationship.getForeignColumns();
					for (Column column : columns) {
						if (column.getName().equals(tableName.getName())) {
							referencedTable.add(table1);
						}
					}*/
				}
			}
		}
		int length = referencedTable.size();
		Table[] referencedTable01 = new Table[length];
		for (int i = 0; i < length; i++) {
			referencedTable01[i] = referencedTable.get(i);
		}
		return referencedTable01;
	}

	public Column[] getBasicColumns(Table table) {
		//System.out.println("显示表名----" + table.getName());
		Column[] columns = table.getColumns();//获得所有列
		Column[] foreignColumns = table.getForeignKeys();//获得充当外键的列
		ArrayList<Column> restColumn = new ArrayList<Column>();
		// ---------------------------------------
		for (Column column : columns) {
			int flag = 0;
			if (!column.isPrimaryKey()) {
				//System.out.println(column.getName());
				if (foreignColumns.length == 0) {
					restColumn.add(column);
					flag = 1;

				} else {
					for (Column foreignKey : foreignColumns) {
						if (column.equals(foreignKey)) {
							flag = 1;
						}
					}
				}
				if (flag == 0) {
					restColumn.add(column);
				}
			}
		}
		// -----------------------------------------
		/*for (int i = 0; i < restColumn.size(); i++) {
			System.out.println(table.getName() + "剩余的Columns      " + restColumn.get(i).getName());
		}*/
		int length = restColumn.size();
		Column[] rest = new Column[length];
		for (int i = 0; i < length; i++) {
			rest[i] = restColumn.get(i);
		}
		return rest;
	}

	// -------------------------------------------------------------------------------------
	public Schema[] getAllSchemas(String DataBaseType, String IPAddress, String Port, String UserName, String PassWord)
			throws SQLException, ClassNotFoundException {
		String info_schema = "information_schema";
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://" + IPAddress + ":" + Port + "/" + info_schema,
				UserName, PassWord);
		//con.setNetworkTimeout(null, 1000000000);
		DataContext dataContext = DataContextFactory.createJdbcDataContext(con);
		Schema[] schemas = dataContext.getSchemas();
		//con.close();
		return schemas;
	}
	
	// -------------------------------------------------------------------------------------
		public static ResultSet getResultSet(String schemaName,String tableName, String IPAddress, String Port, String UserName, String PassWord)
				throws SQLException, ClassNotFoundException {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://" + IPAddress + ":" + Port + "/" + schemaName,
					UserName, PassWord);
			//con.setNetworkTimeout(null, 1000000000);
			Statement stmt = connection.createStatement();
			
			System.out.println("执行Query之前");
			
	        String query = "DESC "+tableName;

	        ResultSet rs = stmt.executeQuery(query);
	        System.out.println("执行Query之后");
			return rs;
		}
}